package com.css.fxfzypg.modules.saresults.repository.impl;

import com.css.fxfzypg.modules.saresults.entity.DzqhQpBasicMapEntity;
import com.css.fxfzypg.modules.saresults.param.DrillHoleQueryParam;
import com.css.fxfzypg.modules.saresults.repository.KzsfResultsRepository;
import com.css.fxfzypg.modules.saresults.vo.DrillHoleQueryVo;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.zcpt.sys.service.SysAreaService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.concurrent.atomic.AtomicReference;

@Repository
public class KzsfResultsRepositoryImpl implements KzsfResultsRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private SysAreaService sysAreaService;

    @Override
    public HashMap<String, Object> getKzsfResultsProject(DzqhQpBasicMapEntity qpBasicMapEntity, int page, int size, String orgId, String provinceId, String userId,String geom) {
        StringBuilder sql = new StringBuilder("SELECT DISTINCT (project_id), * FROM ( SELECT project_id, log_id, project_name, longitude, latitude, administrative_region, sub_date, review_unit, undertaker, safety_rating AS sr, registrant, create_time, registration_unit, status, is_delete, province_id, org_id, approval_status, sub_status, update_status, create_user, original_data_id, model FROM dzqh_ap_basic_map GROUP BY project_id UNION ALL SELECT project_id, log_id, project_name, longitude, latitude, administrative_region, sub_date, review_unit, undertaker, extend1 AS sr, registrant, create_time, registration_unit, status, is_delete, province_id, org_id, approval_status, sub_status, update_status, create_user, original_data_id, model FROM dzqh_qp_basic_map GROUP BY project_id UNION ALL SELECT project_id, log_id, project_name, longitude, latitude, administrative_region, sub_date, review_unit, undertaker, extend1 AS sr, registrant, create_time, registration_unit, status, is_delete, province_id, org_id, approval_status, sub_status, update_status, create_user, original_data_id, model FROM dzqh_xqh_basic_map GROUP BY project_id ) AS dzqh WHERE sub_status = '1' AND update_status = '0'");
        if (qpBasicMapEntity.getRole().equals("3")) {
            sql.append(" and is_delete='0' and create_user='" + userId + "'");
        }
        if (qpBasicMapEntity.getRole().equals("1")) {
            sql.append(" and province_id='" + provinceId + "'");
        }
        if (qpBasicMapEntity.getRole().equals("2")) {
            //sql.append(" and province_id='" + provinceId + "' or org_id='" + orgId + "'");
            sql.append(" and (province_id='" + provinceId + "' or org_id='" + orgId + "')");
        }
        if (!PlatformObjectUtils.isEmpty(qpBasicMapEntity.getProjectName())) {
            sql.append(" and project_name like '%" + qpBasicMapEntity.getProjectName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(qpBasicMapEntity.getLongitude())) {
            sql.append(" and longitude >= '"+qpBasicMapEntity.getLongitude()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(qpBasicMapEntity.getEndLongitude())){
            sql.append(" and longitude <= '"+qpBasicMapEntity.getEndLongitude()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(qpBasicMapEntity.getLatitude())) {
            sql.append(" and latitude >= '"+qpBasicMapEntity.getLatitude()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(qpBasicMapEntity.getEndLatitude())){
            sql.append(" and latitude <= '"+qpBasicMapEntity.getEndLatitude()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(qpBasicMapEntity.getApprovalStatus())) {
            sql.append(" and approval_status ='" + qpBasicMapEntity.getApprovalStatus() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(qpBasicMapEntity.getReviewUnit())) {
            sql.append(" and review_unit like '%" + qpBasicMapEntity.getReviewUnit() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(qpBasicMapEntity.getStatus())) {
            sql.append(" and status = '" + qpBasicMapEntity.getStatus() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(qpBasicMapEntity.getAdministrativeRegion())) {
            sql.append(" and administrative_region= '" + qpBasicMapEntity.getAdministrativeRegion() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(qpBasicMapEntity.getUndertaker())) {
            sql.append(" and undertaker like '%" + qpBasicMapEntity.getUndertaker() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(qpBasicMapEntity.getRegistrant())) {
            sql.append(" and registrant like '%" + qpBasicMapEntity.getRegistrant() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(qpBasicMapEntity.getRegistrationUnit())) {
            sql.append(" and registration_unit like '%" + qpBasicMapEntity.getRegistrationUnit() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(qpBasicMapEntity.getSr())) {
            sql.append(" and sr ='" + qpBasicMapEntity.getSr() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(qpBasicMapEntity.getCreateStartTime())) {
            sql.append(" and date(create_time) >= '"+qpBasicMapEntity.getCreateStartTime()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(qpBasicMapEntity.getCreateEndTime())){
            sql.append(" and date(create_time) <= '"+qpBasicMapEntity.getCreateEndTime()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(qpBasicMapEntity.getSubStartTime()) && !PlatformObjectUtils.isEmpty(qpBasicMapEntity.getSubEndTime())) {
            sql.append(" and sub_date >= '" + qpBasicMapEntity.getSubStartTime() + "' and sub_date <='" + qpBasicMapEntity.getSubEndTime() + "'");
        }

        //地图圈选范围查询
        if (!PlatformObjectUtils.isEmpty(geom)) {
            //项目位置上传文件空间位置相交
            sql.append(" and geom_text is not null and geom_text!='' and ST_Intersects(st_geometryfromtext(geom_text,4490),st_geometryfromtext('"+geom+"',4490))");
            //项目位置经纬度相交查询
            //sql.append(" and ST_Intersects(st_geometryfromtext('POINT(' || longitude || ' ' || latitude || ')',4490),st_geometryfromtext('"+geomText+"',4490))");
        }

        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhQpBasicMapEntity.class)).size();
        StringBuilder limitSql = new StringBuilder(" limit " + size + " offset " + (page - 1) * size);
        sql.append(orderSql).append(limitSql);
        List<DzqhQpBasicMapEntity> qpBasicMapEntityList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhQpBasicMapEntity.class));
        for (DzqhQpBasicMapEntity qpBasicMap : qpBasicMapEntityList) {
            String region = qpBasicMap.getAdministrativeRegion();
            qpBasicMap.setAdministrativeRegion(sysAreaService.getAreaNameByAreaId(region));
        }
        HashMap<String, Object> resultMap = new HashMap();
        resultMap.put("total", total);
        resultMap.put("rows", qpBasicMapEntityList);
        return resultMap;
    }

    @Autowired
    private DrillHoleQueryRepositoryImpl drillHoleQueryRepository;

    @Override
    public HashMap<String, Object> getKzsfResultsHl(DrillHoleQueryParam param, int page, int size, String orgId, String provinceId, String userId, String geom) {
//        StringBuilder sql = new StringBuilder(" select DISTINCT(dzqh.project_id), dzqh.* from (" +
//                " select project_id,project_name,array_to_string(array (select unnest(array_agg(longitude))),',') as longitude,array_to_string(array (select unnest(array_agg(latitude))),',') as latitude,administrative_region,sub_date,review_unit,undertaker,safety_rating as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,update_status,sub_status,create_user,model from dzqh_ap_basic_map group by project_id" +
//                " UNION ALL select project_id,project_name,array_to_string(array (select unnest(array_agg(longitude))),',') as longitude,array_to_string(array (select unnest(array_agg(latitude))),',') as latitude,administrative_region,sub_date,review_unit,undertaker,extend1 as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,update_status,sub_status,create_user,model from dzqh_qp_basic_map group by project_id" +
//                " UNION ALL select project_id,project_name,array_to_string(array (select unnest(array_agg(longitude))),',') as longitude,array_to_string(array (select unnest(array_agg(latitude))),',') as latitude,administrative_region,sub_date,review_unit,undertaker,extend1 as sr,registrant,create_time,registration_unit,status,is_delete,province_id,org_id,approval_status,update_status,sub_status,create_user,model from dzqh_xqh_basic_map group by project_id" +
//                " ) as dzqh " +
//                " left join (select *,array_to_string(array (select unnest(array_agg(hl_longitude))),',') as longitude,array_to_string(array (select unnest(array_agg(hl_latitude))),',')from dzqh_ap_crux_layer_pf group by pf_id) as daclp on daclp.project_id = dzqh.project_id \n" +
//                " left join dzqh_ap_crux_sd dacsd on dacsd.project_id=dzqh.project_id " +
//                " left join dzqh_ap_crux_sc dacsc on dacsc.project_id=dzqh.project_id " +
//                " left join dzqh_ap_crux_sd dacs on dacs.project_id= dzqh.project_id where dzqh.sub_status='1' and dzqh.update_status='0'");
        StringBuilder sql = new StringBuilder("SELECT daclp.*, dacs.field_type FROM( SELECT project_id, '1' as y06_exist, '1' as y08_exist, project_name, longitude, latitude, administrative_region, sub_date, review_unit, undertaker, safety_rating AS sr, registrant, create_time, registration_unit, status, is_delete, province_id, org_id, approval_status, update_status, sub_status, create_user, model, geom_text FROM dzqh_ap_basic_map UNION ALL SELECT project_id, '1' as y06_exist, '1' as y08_exist, project_name, longitude, latitude, administrative_region, sub_date, review_unit, undertaker, extend1 AS sr, registrant, create_time, registration_unit, status, is_delete, province_id, org_id, approval_status, update_status, sub_status, create_user, model, geom_text FROM dzqh_qp_basic_map UNION ALL SELECT project_id, '1' as y06_exist, '1' as y08_exist, project_name, longitude, latitude, administrative_region, sub_date, review_unit, undertaker, extend1 AS sr, registrant, create_time, registration_unit, status, is_delete, province_id, org_id, approval_status, update_status, sub_status, create_user, model, geom_text FROM dzqh_xqh_basic_map) AS dzqh LEFT JOIN dzqh_ap_crux_layer_pf daclp ON daclp.project_id = dzqh.project_id LEFT JOIN dzqh_ap_crux_sc dacs ON dacs.pf_id = daclp.pf_id WHERE dzqh.sub_status = '1' AND dzqh.update_status = '0'  ");
        if (param.getRole().equals("3")){
            sql.append(" and dzqh.is_delete='0' and dzqh.create_user='"+userId+"'");
        }
        if (param.getRole().equals("1")){
            sql.append(" and dzqh.province_id='"+provinceId+"'");
        }
        if (param.getRole().equals("2")){
            //sql.append(" and dzqh.province_id='"+provinceId +"' or dzqh.org_id='"+orgId+"'");
            sql.append(" and (dzqh.province_id='"+provinceId +"' or dzqh.org_id='"+orgId+"')");
        }
        if (PlatformObjectUtils.isNotEmpty(geom)) {
            sql.append(" and hl_longitude is not null and hl_latitude is not null and hl_longitude!='' and hl_latitude!='' and ST_Intersects(st_geometryfromtext('POINT(' || hl_longitude || ' ' || hl_latitude || ')',4490),st_geometryfromtext('"+geom+"',4490))");
        }

//        if(!roleList.contains(FxfzConstants.DZQH_GL_ROLE)){
//            sql.append(" and model != '普查数据'");
//        }
        if (PlatformObjectUtils.isNotEmpty(param.getProjectName())) {
            sql.append(" and daclp.project_name like '%" + param.getProjectName() + "%'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartHlLongitude()) && PlatformObjectUtils.isNotEmpty(param.getEndHlLongitude())) {
            sql.append(" and hl_longitude between '" + param.getStartHlLongitude() + "' and '" + param.getEndHlLongitude() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartHlLatitude()) && PlatformObjectUtils.isNotEmpty(param.getEndHlLatitude())) {
            sql.append(" and hl_latitude between '" + param.getStartHlLatitude() + "' and '" + param.getEndHlLatitude() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getDataType())) {
            sql.append(" and model = '" + param.getDataType() + "'");
        }
//        if (PlatformObjectUtils.isNotEmpty(param.getSoilDynamic())) {
//            sql.append(" and y06_exist = '" + param.getSoilDynamic() + "'");
//        }
//        if (PlatformObjectUtils.isNotEmpty(param.getMeasuredValues())) {
//            sql.append(" and y08_exist = '" + param.getMeasuredValues() + "'");
//        }
        if (PlatformObjectUtils.isNotEmpty(param.getSiteType())) {
            sql.append(" and field_type = '" + param.getSiteType() + "'");
        }

        sql.append(" and daclp.project_id is not null and daclp.hl_longitude is not null and daclp.hl_latitude is not null");
        //地图圈选范围查询
        if (!PlatformObjectUtils.isEmpty(geom)) {
            //项目信息相交
            //sql.append(" and dzqh.geom_text is not null and dzqh.geom_text!='' and ST_Intersects(st_geometryfromtext(dzqh.geom_text,4490),st_geometryfromtext('"+geomText+"',4490))");
            //孔位信息相交
            sql.append(" and ST_Intersects(st_geometryfromtext('POINT(' || daclp.hl_longitude || ' ' || daclp.hl_latitude || ')',4490),st_geometryfromtext('"+geom+"',4490))");
        }

        StringBuilder orderSql = new StringBuilder(" order by dzqh.create_time desc");
        sql.append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhQpBasicMapEntity.class)).size();
//        StringBuilder limitSql = new StringBuilder(" limit " + size + " offset " + (page - 1) * size);
//        sql.append(limitSql);
        List<DrillHoleQueryVo> result = new ArrayList<>();
        List<DrillHoleQueryVo> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DrillHoleQueryVo.class));
        AtomicReference<Long> num = new AtomicReference<>(0L);
        for (DrillHoleQueryVo it : list) {
            if (PlatformObjectUtils.isNotEmpty(it.getDataType())) {
                Integer status = drillHoleQueryRepository.getY06ExistStatus(it.getPfId(), it.getProjectId());
                if (status != 0) {
                    it.setY06Exist("1");
                } else {
                    it.setY06Exist("0");
                }
                Integer status1 = drillHoleQueryRepository.getY08ExistStatus(it.getPfId(), it.getProjectId());
                if (status1 != 0) {
                    it.setY08Exist("1");
                } else {
                    it.setY08Exist("0");
                }
                if(StringUtils.isNotBlank(param.getSoilDynamic()) && StringUtils.isNotBlank(param.getMeasuredValues())){
                    if(it.getY06Exist().equals(param.getSoilDynamic()) && it.getY08Exist().equals(param.getMeasuredValues())){
                        it.setNum(num.getAndSet(num.get() + 1));
                        result.add(it);
                    }
                }else if(StringUtils.isNotBlank(param.getSoilDynamic()) && StringUtils.isBlank(param.getMeasuredValues())){
                    if(it.getY06Exist().equals(param.getSoilDynamic())){
                        it.setNum(num.getAndSet(num.get() + 1));
                        result.add(it);
                    }
                }else if(StringUtils.isBlank(param.getSoilDynamic()) && StringUtils.isNotBlank(param.getMeasuredValues())){
                    if(it.getY08Exist().equals(param.getMeasuredValues())){
                        it.setNum(num.getAndSet(num.get() + 1));
                        result.add(it);
                    }
                }else{
                    it.setNum(num.getAndSet(num.get() + 1));
                    result.add(it);
                }
            }else{
                it.setNum(num.getAndSet(num.get() + 1));
                result.add(it);
            }
        }
//        list.parallelStream().forEach(it -> {
//            if (PlatformObjectUtils.isNotEmpty(it.getDataType())) {
//                Long aLong = num.get();
//                Integer status = drillHoleQueryRepository.getY06ExistStatus(it.getPfId(), it.getProjectId());
//                if (status != 0) {
//                    it.setY06Exist("1");
//                } else {
//                    it.setY06Exist("0");
//                }
//                Integer status1 = drillHoleQueryRepository.getY08ExistStatus(it.getPfId(), it.getProjectId());
//                if (status1 != 0) {
//                    it.setY08Exist("1");
//                } else {
//                    it.setY08Exist("0");
//                }
//                if(StringUtils.isNotBlank(param.getSoilDynamic()) && StringUtils.isNotBlank(param.getMeasuredValues())){
//                    if(it.getY06Exist().equals(param.getSoilDynamic()) && it.getY08Exist().equals(param.getMeasuredValues())){
//                        it.setNum(num.getAndSet(num.get() + 1));
//                        result.add(it);
//                    }
//                }else if(StringUtils.isNotBlank(param.getSoilDynamic()) && StringUtils.isBlank(param.getMeasuredValues())){
//                    if(it.getY06Exist().equals(param.getSoilDynamic())){
//                        it.setNum(num.getAndSet(num.get() + 1));
//                        result.add(it);
//                    }
//                }else if(StringUtils.isBlank(param.getSoilDynamic()) && StringUtils.isNotBlank(param.getMeasuredValues())){
//                    if(it.getY08Exist().equals(param.getMeasuredValues())){
//                        it.setNum(num.getAndSet(num.get() + 1));
//                        result.add(it);
//                    }
//                }else{
//                    it.setNum(num.getAndSet(num.get() + 1));
//                    result.add(it);
//                }
//            }else{
//                it.setNum(num.getAndSet(num.get() + 1));
//                result.add(it);
//            }
//        });
        List<DrillHoleQueryVo> drillHoleQueryVos = new ArrayList<>();
        if(result.size()>0){
            int startIndex = (page - 1) * size;
            int endIndex = Math.min(startIndex + size, result.size());
            drillHoleQueryVos = result.subList(startIndex, endIndex);
        }
        HashMap<String, Object> resultMap = new HashMap();
//        resultMap.put("total", total);
//        resultMap.put("rows", list);
        resultMap.put("total", result.size());
        resultMap.put("rows", drillHoleQueryVos);
        return resultMap;
    }

    @Override
    public List<DrillHoleQueryVo> getKzsfResultsHl(DrillHoleQueryParam param, String orgId, String provinceId, String userId) {
        StringBuilder sql = new StringBuilder("SELECT daclp.*, dacs.field_type FROM( SELECT project_id, '1' as y06_exist, '1' as y08_exist, project_name, longitude, latitude, administrative_region, sub_date, review_unit, undertaker, safety_rating AS sr, registrant, create_time, registration_unit, status, is_delete, province_id, org_id, approval_status, update_status, sub_status, create_user, model, geom_text FROM dzqh_ap_basic_map UNION ALL SELECT project_id, '1' as y06_exist, '1' as y08_exist, project_name, longitude, latitude, administrative_region, sub_date, review_unit, undertaker, extend1 AS sr, registrant, create_time, registration_unit, status, is_delete, province_id, org_id, approval_status, update_status, sub_status, create_user, model, geom_text FROM dzqh_qp_basic_map UNION ALL SELECT project_id, '1' as y06_exist, '1' as y08_exist, project_name, longitude, latitude, administrative_region, sub_date, review_unit, undertaker, extend1 AS sr, registrant, create_time, registration_unit, status, is_delete, province_id, org_id, approval_status, update_status, sub_status, create_user, model, geom_text FROM dzqh_xqh_basic_map) AS dzqh LEFT JOIN dzqh_ap_crux_layer_pf daclp ON daclp.project_id = dzqh.project_id LEFT JOIN dzqh_ap_crux_sc dacs ON dacs.pf_id = daclp.pf_id WHERE dzqh.sub_status = '1' AND dzqh.update_status = '0'  ");
        if (param.getRole().equals("3")){
            sql.append(" and dzqh.is_delete='0' and dzqh.create_user='"+userId+"'");
        }
        if (param.getRole().equals("1")){
            sql.append(" and dzqh.province_id='"+provinceId+"'");
        }
        if (param.getRole().equals("2")){
            //sql.append(" and dzqh.province_id='"+provinceId +"' or dzqh.org_id='"+orgId+"'");
            sql.append(" and (dzqh.province_id='"+provinceId +"' or dzqh.org_id='"+orgId+"')");
        }

//        if(!roleList.contains(FxfzConstants.DZQH_GL_ROLE)){
//            sql.append(" and model != '普查数据'");
//        }
        if (PlatformObjectUtils.isNotEmpty(param.getProjectName())) {
            sql.append(" and daclp.project_name like '%" + param.getProjectName() + "%'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartHlLongitude()) && PlatformObjectUtils.isNotEmpty(param.getEndHlLongitude())) {
            sql.append(" and hl_longitude between '" + param.getStartHlLongitude() + "' and '" + param.getEndHlLongitude() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartHlLatitude()) && PlatformObjectUtils.isNotEmpty(param.getEndHlLatitude())) {
            sql.append(" and hl_latitude between '" + param.getStartHlLatitude() + "' and '" + param.getEndHlLatitude() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getDataType())) {
            sql.append(" and model = '" + param.getDataType() + "'");
        }

        if (PlatformObjectUtils.isNotEmpty(param.getSiteType())) {
            sql.append(" and field_type = '" + param.getSiteType() + "'");
        }

        sql.append(" and daclp.project_id is not null and daclp.hl_longitude is not null and daclp.hl_latitude is not null");
        StringBuilder orderSql = new StringBuilder(" order by dzqh.create_time desc");
        sql.append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzqhQpBasicMapEntity.class)).size();
        List<DrillHoleQueryVo> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DrillHoleQueryVo.class));
        List<DrillHoleQueryVo> result = new ArrayList<>();
        list.forEach(it -> {
            if(StringUtils.isBlank(it.getFieldType())){
                it.setFieldType("无");
            }
            if (PlatformObjectUtils.isNotEmpty(it.getDataType())) {
                Integer status = drillHoleQueryRepository.getY06ExistStatus(it.getPfId(), it.getProjectId());
                if (status != 0) {
                    it.setY06Exist("1");
                } else {
                    it.setY06Exist("0");
                }
                Integer status1 = drillHoleQueryRepository.getY08ExistStatus(it.getPfId(), it.getProjectId());
                if (status1 != 0) {
                    it.setY08Exist("1");
                } else {
                    it.setY08Exist("0");
                }
                if (PlatformObjectUtils.isNotEmpty(param.getSoilDynamic()) && PlatformObjectUtils.isNotEmpty(param.getMeasuredValues())) {
                    if(it.getY06Exist().equals(param.getSoilDynamic()) && it.getY08Exist().equals(param.getMeasuredValues())){
                        result.add(it);
                    }
                }else if (PlatformObjectUtils.isNotEmpty(param.getSoilDynamic()) && !PlatformObjectUtils.isNotEmpty(param.getMeasuredValues())) {
                    if(it.getY06Exist().equals(param.getSoilDynamic())){
                        result.add(it);
                    }
                }else if (!PlatformObjectUtils.isNotEmpty(param.getSoilDynamic()) && PlatformObjectUtils.isNotEmpty(param.getMeasuredValues())) {
                    if( it.getY08Exist().equals(param.getMeasuredValues())){
                        result.add(it);
                    }
                }else{
                    result.add(it);
                }

            }else{
                result.add(it);
            }
        });
        return result;
    }

    private Integer getY08ExistStatus(String pfId, String projectId) {
        String sql = "";
        if (projectId != null) {
            sql = "select count(1) from dzqh_ap_crux_dh where project_id = '" + projectId + "'";
        } else {
            sql = "select count(1) from dzqh_ap_crux_dh where pf_id = '" + pfId + "'";
        }
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    @Override
    public List<String> getDataId(String userId,String orgId, String provinceId, DrillHoleQueryParam param) {
        StringBuffer sql = new StringBuffer("SELECT DISTINCT dzqh.project_id FROM ( SELECT project_id, '1' AS y06_exist, '1' AS y08_exist, project_name, longitude, latitude, administrative_region, sub_date, review_unit, undertaker, safety_rating AS sr, registrant, create_time, registration_unit, status, is_delete, province_id, org_id, approval_status, update_status, sub_status, create_user, model, geom_text FROM dzqh_ap_basic_map UNION ALL SELECT project_id, '1' AS y06_exist, '1' AS y08_exist, project_name, longitude, latitude, administrative_region, sub_date, review_unit, undertaker, extend1 AS sr, registrant, create_time, registration_unit, status, is_delete, province_id, org_id, approval_status, update_status, sub_status, create_user, model, geom_text FROM dzqh_qp_basic_map UNION ALL SELECT project_id, '1' AS y06_exist, '1' AS y08_exist, project_name, longitude, latitude, administrative_region, sub_date, review_unit, undertaker, extend1 AS sr, registrant, create_time, registration_unit, status, is_delete, province_id, org_id, approval_status, update_status, sub_status, create_user, model, geom_text FROM dzqh_xqh_basic_map ) AS dzqh LEFT JOIN dzqh_ap_crux_layer_pf daclp ON daclp.project_id = dzqh.project_id LEFT JOIN dzqh_ap_crux_sc dacs ON dacs.pf_id = daclp.pf_id WHERE dzqh.sub_status = '1' AND dzqh.update_status = '0' AND daclp.project_id IS NOT NULL AND daclp.hl_longitude IS NOT NULL AND daclp.hl_latitude IS NOT NULL");
        if (param.getRole().equals("3")){
            sql.append(" and dzqh.is_delete='0' and dzqh.create_user='"+userId+"'");
        }
        if (param.getRole().equals("1")){
            sql.append(" and dzqh.province_id='"+provinceId+"'");
        }
        if (param.getRole().equals("2")){
            //sql.append(" and dzqh.province_id='"+provinceId +"' or dzqh.org_id='"+orgId+"'");
            sql.append(" and (dzqh.province_id='"+provinceId +"' or dzqh.org_id='"+orgId+"')");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getGeom())) {
            sql.append(" and hl_longitude is not null and hl_latitude is not null and hl_longitude!='' and hl_latitude!='' and ST_Intersects(st_geometryfromtext('POINT(' || hl_longitude || ' ' || hl_latitude || ')',4490),st_geometryfromtext('"+param.getGeom()+"',4490))");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProjectName())) {
            sql.append(" and daclp.project_name like '%" + param.getProjectName() + "%'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartHlLongitude()) && PlatformObjectUtils.isNotEmpty(param.getEndHlLongitude())) {
            sql.append(" and hl_longitude between '" + param.getStartHlLongitude() + "' and '" + param.getEndHlLongitude() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getStartHlLatitude()) && PlatformObjectUtils.isNotEmpty(param.getEndHlLatitude())) {
            sql.append(" and hl_latitude between '" + param.getStartHlLatitude() + "' and '" + param.getEndHlLatitude() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getDataType())) {
            sql.append(" and model = '" + param.getDataType() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getSoilDynamic())) {
            sql.append(" and y06_exist = '" + param.getSoilDynamic() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getMeasuredValues())) {
            sql.append(" and y08_exist = '" + param.getMeasuredValues() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getSiteType())) {
            sql.append(" and field_type = '" + param.getSiteType() + "'");
        }

        sql.append(" and daclp.project_id is not null and daclp.hl_longitude is not null and daclp.hl_latitude is not null");
        //地图圈选范围查询
        if (!PlatformObjectUtils.isEmpty(param.getGeom())) {
            //项目信息相交
            //sql.append(" and dzqh.geom_text is not null and dzqh.geom_text!='' and ST_Intersects(st_geometryfromtext(dzqh.geom_text,4490),st_geometryfromtext('"+geomText+"',4490))");
            //孔位信息相交
            sql.append(" and ST_Intersects(st_geometryfromtext('POINT(' || daclp.hl_longitude || ' ' || daclp.hl_latitude || ')',4490),st_geometryfromtext('"+param.getGeom()+"',4490))");
        }
        return jdbcTemplate.queryForList(sql.toString(), String.class);
    }
}
